﻿#region References
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
#endregion

namespace ElectronicsStore
{
	public partial class MainForm : Form
	{
		protected static readonly string NewLine = Environment.NewLine;

		protected IList objects = null;
		protected BindingSource bindingSource = null;
		protected electronicsEntities context;
		protected invoice activeInvoice;
		protected List<product> products; 

		public MainForm ()
		{
			InitializeComponent();
		}

		/// <summary>
		/// Occurs when form loads.
		/// </summary>
		protected virtual void OnMainFormLoad (object sender, EventArgs e)
		{
			context = new electronicsEntities();
			OnTableSelect(null, null);
		}

		/// <summary>
		/// Occurs when selects tab in main control.
		/// </summary>
		private void MainControlSelectedIndexChanged (object sender, EventArgs e)
		{
			switch (mainControl.SelectedIndex)
			{
				case 0: // Database
					break;

				case 1: // Query
					UpdateStorageProductsTotalsQuery();
					break;

				case 2: // Invoices
					UpdateInvoicesMenu();
					break;
			}
		}

		private void UpdateInvoicesMenu ()
		{
			invoiceSelectBindingSource.DataSource = context.invoice.ToList();
			invoiceSelectNavigator.BindingSource = invoiceSelectBindingSource;
			invoiceSelectNavigator.Refresh();

			products = context.product.ToList();
			invoiceProductComboBox.DataSource = products;
			invoiceProductComboBox.ValueMember = "ID";
			invoiceProductComboBox.DisplayMember = "Name";
		}

		/// <summary>
		/// Occurs when user selects database table tab.
		/// </summary>
		protected virtual void OnTableSelect (object sender, TabControlEventArgs e)
		{
			// Select binding source & objects
			switch (tableControl.SelectedTab.Text)
			{
				case "Products":
					bindingSource = productBindingSource;
					objects = context.product.ToList();
					break;

				case "Invoices":
					bindingSource = invoiceBindingSource;
					objects = context.invoice.ToList();
					break;

				case "Details":
					bindingSource = invoice_detailsBindingSource;
					objects = context.invoice_details.ToList();
					break;

				case "Storage":
					bindingSource = storageBindingSource;
					objects = context.storage.ToList();
					break;

				case "Manufacturers":
					bindingSource = manufacturerBindingSource;
					objects = context.manufacturer.ToList();
					break;
			}
			// Update binding source
			if (bindingSource == null) return;
			bindingSource.DataSource = objects;
			bindingNavigator.BindingSource = bindingSource;
			bindingNavigator.Refresh();
		}

		/// <summary>
		/// Occurs when click on create button.
		/// </summary>
		protected virtual void OnCreateClick (object sender, EventArgs e)
		{
			object entity;
			// Create new entity
			if (bindingSource == productBindingSource)
			{
				product value;
				entity = value = context.product.Create();
				context.product.Add(value);
			}
			else if (bindingSource == invoiceBindingSource)
			{
				invoice value;
				entity = value = context.invoice.Create();
				context.invoice.Add(value);
			}
			else if (bindingSource == invoice_detailsBindingSource)
			{
				invoice_details value;
				entity = value = context.invoice_details.Create();
				context.invoice_details.Add(value);
			}
			else if (bindingSource == storageBindingSource)
			{
				storage value;
				entity = value = context.storage.Create();
				context.storage.Add(value);
			}
			else if (bindingSource == manufacturerBindingSource)
			{
				manufacturer value;
				entity = value = context.manufacturer.Create();
				context.manufacturer.Add(value);
			}
			else return;

			try
			{
				// Save changes to database
				context.SaveChanges();

				// Add to display data
				bindingSource.Add(entity);

				// Update navigation
				bindingNavigator.Refresh();
			}
			catch (Exception ex)
			{
				MessageBox.Show("Unhandled exception: " + ex);
			}
		}

		/// <summary>
		/// Occurs when click on save button.
		/// </summary>
		protected virtual void OnSaveClick (object sender, EventArgs e)
		{
			context.SaveChanges();
		}

		/// <summary>
		/// Occurs when click on delete button.
		/// </summary>
		protected virtual void OnDeleteClick (object sender, EventArgs e)
		{
			if (bindingSource == null) return;
			// Save active entity
			var entity = bindingSource.Current;
			if (entity == null) return;
			// Remove from display data
			bindingSource.RemoveCurrent();
			try
			{
				// Remove from database
				if (bindingSource == productBindingSource) context.product.Remove(entity as product);
				else if (bindingSource == invoiceBindingSource) context.invoice.Remove(entity as invoice);
				else if (bindingSource == invoice_detailsBindingSource) context.invoice_details.Remove(entity as invoice_details);
				else if (bindingSource == storageBindingSource) context.storage.Remove(entity as storage);
				else if (bindingSource == manufacturerBindingSource) context.manufacturer.Remove(entity as manufacturer);
			}
			finally
			{
				context.SaveChanges();
				bindingNavigator.Refresh();
			}
		}

		private void ManufacturerIDComboBoxDropDown (object sender, EventArgs e)
		{
			var comboBox = (ComboBox)sender;
			comboBox.Items.Clear();
			foreach (var entity in context.manufacturer)
				comboBox.Items.Add(string.Format("[ID:{0}] {1}", entity.ID, entity.Name));
		}

		/// <summary>
		/// Executes MySQL query & displays results into grid view.
		/// </summary>
		/// <param name="query">Query to execute.</param>
		protected void ExecuteMySqlQuery (string query)
		{
			mySqlQueryText.Text = query;
			// Remove NewLine characters
			query = query.Replace(Environment.NewLine, string.Empty);
			using (var connection = new MySqlConnection(context.Database.Connection.ConnectionString))
			{
				connection.Open();
				using (var sqlCommand = new MySqlCommand(query, connection))
				{
					var reader = sqlCommand.ExecuteReader();
					var table = new DataTable();
					table.Load(reader);
					mySqlDataGridView.AutoGenerateColumns = true;
					//mySqlDataGridView.Columns.Clear();
					//foreach (DataColumn column in table.Columns)
					//{
					//	var index = mySqlDataGridView.Columns.Add(column.ColumnName, column.ColumnName);
					//	var c = mySqlDataGridView.Columns[index];
					//	c.DataPropertyName = column.ColumnName;
					//}
					bindingSourceMySQL.DataSource = table;
				}
			}
		}

		/// <summary>
		/// Occurs when click on Execute Query button.
		/// </summary>
		private void ButtonExecuteQueryOnClick (object sender, EventArgs e)
		{
			try
			{
				ExecuteMySqlQuery(mySqlQueryText.Text);
			}
			catch (Exception exception)
			{
				MessageBox.Show(exception.ToString());
			}
		}

		#region #1
		private void TextBox1TextChanged (object sender, EventArgs e)
		{
			UpdateStorageProductsTotalsQuery();
		}

		private void UpdateStorageProductsTotalsQuery ()
		{
			var textBox = textBox1;
			var query = Query.GetStorageProductsTotals(textBox.Text);
			mySqlQueryText.Text = query;
		}
		#endregion

		private void TabControl1SelectedIndexChanged (object sender, EventArgs e)
		{
			switch (tabControl1.SelectedIndex)
			{
				case 0: // Query #1
					UpdateStorageProductsTotalsQuery();
					break;
			}
		}

		#region Invoices Menu
		private void OnInvoiceCreateClick (object sender, EventArgs e)
		{
			// Create invoice
			var invoice = context.invoice.Create();
			context.invoice.Add(invoice);
			invoiceSelectBindingSource.Add(invoice);
			invoiceSelectNavigator.Refresh();
		}

		private void OnInvoiceDeleteClick (object sender, EventArgs e)
		{
			// Remove invoice
			var invoice = (invoice)invoiceSelectBindingSource.Current;
			context.invoice.Remove(invoice);
			invoiceSelectBindingSource.Remove(invoice);
			invoiceSelectGridView.Refresh();
		}

		private void OnInvoiceSaveClick (object sender, EventArgs e)
		{
			try
			{
				context.SaveChanges();
				invoiceDetailsGridView.Refresh();
			}
			catch (Exception exception)
			{
				MessageBox.Show(exception.ToString());
			}
		}

		private void OnInvoiceCreateDetailsClick (object sender, EventArgs e)
		{
			// Create details
			var details = context.invoice_details.Create();
			var product = (product)invoiceProductComboBox.SelectedItem;

			// Values
			details.InvoiceID = activeInvoice.ID;
			details.ProductID = product.ID;
			details.Count = (int)invoiceProductCount.Value;
			context.invoice_details.Add(details);

			// Refresh view
			var dataSource = (BindingList<object>)invoiceDetailsGridView.DataSource;
			dataSource.Add(details);
		}

		private void OnInvoiceSelect (object sender, EventArgs e)
		{
			var invoice = (invoice)invoiceSelectBindingSource.Current;
			// Do not select invoice twice
			if (invoice == null || invoice == activeInvoice) return;
			activeInvoice = invoice;

			var invoiceID = invoice.ID;
			var query = from details in context.invoice_details
						where details.InvoiceID != null && details.InvoiceID == invoiceID
						select details;
						//join p in context.product on details.ProductID equals p.ID 
						//select new
						//{
						//	details.ID,
						//	details.InvoiceID,
						//	details.ProductID,
						//	details.Count,
						//	p.Name,
						//};
			//MessageBox.Show(query.ToString());
			try
			{
				// Refresh view
				invoiceDetailsGridView.AutoGenerateColumns = true;
				//invoiceDetailsGridView.DataSource = query.ToList();
				invoiceDetailsGridView.DataSource = new BindingList<object>(query.ToList().Cast<object>().ToList());

				// Column settings
				var columns = invoiceDetailsGridView.Columns;
				var cProductName = new DataGridViewColumn()
				{
					
				};

				columns.Remove("invoice");
				columns.Remove("product");

				var cID = columns["ID"];
				var cInvoiceID = columns["InvoiceID"];
				cID.ReadOnly = cInvoiceID.ReadOnly = true;
				cID.AutoSizeMode = cInvoiceID.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
				cID.DefaultCellStyle.BackColor = cInvoiceID.DefaultCellStyle.BackColor = Color.LightGray;
				invoiceDetailsGridView.Refresh();
			}
			catch (Exception exception)
			{
				MessageBox.Show(exception.ToString());
			}
		}
		#endregion

		/// <summary>
		/// Contains MySQL queries.
		/// </summary>
		public static class Query
		{
			public static string GetStorageProductsTotals (string productID)
			{
				// Any product
				if (string.IsNullOrWhiteSpace(productID))
				{
					return "SELECT product.`Name`, " + NewLine +
						   "Sum(`storage`.Count) AS TotalCount, " + NewLine +
						   "product.Weight, Sum((storage.Count * product.Weight)) AS TotalWeight " + NewLine +
						   "FROM `storage` , product " + NewLine +
						   "WHERE `storage`.ProductID = product.ID " + NewLine +
						   "GROUP BY product.ID";
				}

				// Product by ID
				return string.Format
					("SELECT product.Name, " + NewLine +
					 "Sum(storage.Count) AS TotalCount, " + NewLine +
					 "product.Weight, Sum((storage.Count * product.Weight)) AS TotalWeight " + NewLine +
					 "FROM storage , product " + NewLine +
					 "WHERE storage.ProductID = product.ID AND product.ID = {0} " + NewLine +
					 "GROUP BY product.ID",
					 productID);
			}
		}


	}
}
